package com.pic.mgt.jdbc;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Timer;
import java.util.TimerTask;
import java.util.Vector;
import java.util.concurrent.ConcurrentHashMap;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.NonTransientDataAccessResourceException;
import org.springframework.jdbc.support.SQLExceptionTranslator;

public final class CachedPreparedStatemntJdbcTemplate implements
		CachedPreparedStatemntJdbcOperations {
	private DataSource dataSource;
	private SQLExceptionTranslator sqlExceptionTranslator;

	private Connection shareConnection;
	private Map<String, PreparedStatementScriptInfo> preparedStatementInfoMap;
	private Map<String, PreparedStatement> preparedStatementPoolMap;

	public CachedPreparedStatemntJdbcTemplate(DataSource dataSource) {
		preparedStatementInfoMap = new ConcurrentHashMap<String, PreparedStatementScriptInfo>();
		preparedStatementPoolMap = new ConcurrentHashMap<String, PreparedStatement>();

		this.dataSource = dataSource;
	}

	@Override
	public boolean addPreparedStatementScript(String preparedStatementScript,
											  long timeout) {
		if (null == preparedStatementScript) {
			return false;
		}

		if (this.preparedStatementInfoMap.containsKey(preparedStatementScript)) {
			// Prepared Statement Id 重覆
			return false;
		} else {
			this.preparedStatementInfoMap.put(
									preparedStatementScript,
									new PreparedStatementScriptInfo(
													preparedStatementScript,
													timeout));

			return true;
		}
	}

	@Override
	public int executeUpdate(String preparedStatementScript,
							 Object[] parameterValueArray) throws DataAccessException {
		PreparedStatementScriptInfo preparedStatementScriptInfo =
								this.preparedStatementInfoMap.get(preparedStatementScript);

		if (null == preparedStatementScriptInfo) {
			return 0;
		}

		try {
			synchronized (preparedStatementScriptInfo) {
				// throws SQLException
				PreparedStatement preparedStatement = getPreparedStatement(preparedStatementScript);
				
				// 標記表示此 PreparedStatement 有執行過
				preparedStatementScriptInfo.doUpdate();
				
				// 給定 PreparedStatement 內的參數值
				assignPreparedStatementParameters(preparedStatement,parameterValueArray);
				
				return
					preparedStatement.executeUpdate();
			}
		} catch (SQLException e) {
			try {
				// 先將 SQLException 轉換為 Spring 的 DataAccessException 再拋出
				DataAccessException dataAccessException =
								this.sqlExceptionTranslator.translate("",
																	  preparedStatementScript,
																	  e);
				throw dataAccessException;
			} catch (NonTransientDataAccessResourceException e1) {
				// NonTransientDataAccessResourceException 是當無法連接至資料庫的各種情形下會發生
				/*
				 * 發生 NonTransientDataAccessResourceException 有可能是 Connection Timeout，或資料
				 * 庫伺服器掛掉等連線的錯誤問題。此時最保險的方式，就是關閉全部的 PreparedStatement，
				 * 當 PreparedStatement Pool 內沒有任何 PreparedStatement 時，才會讓 Connection  Close 
				 * 掉並清空，在下次執行時才能取得新的 Connection。否則若不清空 PreparedStatement Pool，
				 * Connection 不會 close，則會發生每次執行都是拿到這個錯誤無法連線的 Connection。
				 *  
				 */
				removeAllPreparedStatementInPool();
				
				throw e1;
			} catch (DataAccessException e2) {
				/*
				 * 若不是屬於 NonTransientDataAccessResourceException 的 Exception，都是資料庫連線
				 * 狀態正常情況下，所發生的各種資料庫操作問題，可以直接拋出，不需將 PreparedStatement Pool
				 * 清空。 
				 */
				
				throw e2;
			}
		}
	}
	
	private PreparedStatement getPreparedStatement(String preparedStatementScript) throws SQLException {
		PreparedStatement preparedStatement =
					this.preparedStatementPoolMap.get(preparedStatementScript);
		if (null == preparedStatement) {
			// 表示此 PreparedStatement 已經被回收，不存在於 PreparedStatement Pool 中，必須重新 Create
			synchronized (this) {
				// throws SQLException
				preparedStatement = createPreparedStatement(preparedStatementScript);
			}
		}
		return preparedStatement;
	}

	private PreparedStatement createPreparedStatement(
			String preparedStatementScript) throws SQLException {
		// throws SQLException
		Connection connection = getShareConnection();
		
		// throws SQLException
		PreparedStatement preparedStatement = 
						connection.prepareStatement(preparedStatementScript);
		// create 完畢放回 PreparedStatement Pool 中
		synchronized (preparedStatementPoolMap) {
			this.preparedStatementPoolMap.put(preparedStatementScript,
					preparedStatement);
		}

		// 針對此 PreparedStatement 啟動  TimerTask 自動回收機制
		PreparedStatementScriptInfo preparedStatementScriptInfo = 
							preparedStatementInfoMap.get(preparedStatementScript);
		preparedStatementScriptInfo.startAutoReturnMechanism();

		return preparedStatement;
	}

	private void assignPreparedStatementParameters(PreparedStatement preparedStatement,
												   Object[] parameterValueArray)
												throws SQLException {
		preparedStatement.clearParameters();

		for (int i=0;i<parameterValueArray.length;i++) {
			Object parameterValue = parameterValueArray[i];
			setParametersByDiffType(preparedStatement,
									i + 1,
									parameterValue);
		}
	}

	private void setParametersByDiffType(PreparedStatement preparedStatement,
										 int parameterLocation, 
										 Object parameterValue) throws SQLException {
		if (String.class.isInstance(parameterValue)) {
			// throws SQLException
			preparedStatement.setString(parameterLocation,
					(String) parameterValue);
		} else if (Integer.class.isInstance(parameterValue)) {
			// throws SQLException
			preparedStatement.setInt(parameterLocation,
					(Integer) parameterValue);
		} else if (Long.class.isInstance(parameterValue)) {
			// throws SQLException
			preparedStatement
					.setLong(parameterLocation, (Long) parameterValue);
		} else if (Timestamp.class.isInstance(parameterValue)) {
			// throws SQLException
			preparedStatement.setTimestamp(parameterLocation,
					(Timestamp) parameterValue);
		} else if (Float.class.isInstance(parameterValue)) {
			// throws SQLException
			preparedStatement.setFloat(parameterLocation,
					(Float) parameterValue);
		} else if (Double.class.isInstance(parameterValue)) {
			// throws SQLException
			preparedStatement.setDouble(parameterLocation,
					(Double) parameterValue);
		} else if (Date.class.isInstance(parameterValue)) {
			// throws SQLException
			preparedStatement
					.setDate(parameterLocation, (Date) parameterValue);
		}
	}

	private Connection getShareConnection() throws SQLException {
		if (null == this.shareConnection) {
			// throws SQLException
			this.shareConnection = this.dataSource.getConnection();
		}

		return this.shareConnection;
	}
	
	private void removeAllPreparedStatementInPool() {
		synchronized (this.preparedStatementPoolMap) {
			if (!this.preparedStatementPoolMap.isEmpty()) {
				Set<String> preparedStatementScriptSet = this.preparedStatementPoolMap.keySet();
				for (String preparedStatementScript : preparedStatementScriptSet) {
					PreparedStatementScriptInfo preparedStatementScriptInfo =
										this.preparedStatementInfoMap.get(preparedStatementScript);
					// 移除 PreparedStatementInPool in Pool
					removePreparedStatementInPool(preparedStatementScriptInfo);
				}
			}
		}
	}
	
	private void removePreparedStatementInPool(PreparedStatementScriptInfo preparedStatementScriptInfo) {
		synchronized (preparedStatementScriptInfo) {
			String preparedStatementScript =
				preparedStatementScriptInfo.getPreparedStatementScript();
			PreparedStatement preparedStatement = 
					preparedStatementPoolMap.get(preparedStatementScript);
			if (null != preparedStatement) {
				try {
					// throws SQLException
					preparedStatement.close();
				} catch (SQLException e) {
					// TODO 寫 log
				} finally {
					synchronized (preparedStatementPoolMap) {
						preparedStatementPoolMap
							.remove(preparedStatementScript);
					}
					
					preparedStatementScriptInfo.stopAutoReturnMechanism();
				}
			}
			
			// 檢查若都沒有 PreparedStatement 在 PreparedStatement Pool 中，則關閉
			returnShareConnectionIfNoActivePreparedStatement();
		}
	}
	
	private void returnShareConnectionIfNoActivePreparedStatement() {
		// 檢查若都沒有 PreparedStatement 在 PreparedStatement Pool 中，則關閉
		synchronized (this) {
			if (preparedStatementPoolMap.isEmpty()) {
				if (null != shareConnection) {
					try {
						// throws SQLException
						shareConnection.close();
					} catch (SQLException e) {
						// TODO 寫 log
					} finally {
						shareConnection = null;
					}
				}
			}
		}
	}
	
	@Override
	public void removePreparedStatementScript(String preparedStatementScript) {
		PreparedStatementScriptInfo preparedStatementScriptInfo =
							preparedStatementInfoMap.get(preparedStatementScript);
		
		if (null == preparedStatementScriptInfo) {
			return;
		}
		
		removePreparedStatementInPool(preparedStatementScriptInfo);
		
		preparedStatementInfoMap.remove(preparedStatementScript);
	}
	
	

	/**
	 * 
	 * @author p10118178
	 * 
	 */
	private class PreparedStatementScriptInfo {
		private String preparedStatementScript;
		private long timeout;
		private boolean motionCheck = true;
		private Timer autoReturnStatementTimer;
		private TimerTask autoReturnStatementTimerTask;
		private final String TIMERNAME = "JDBC Auto Return Mechanism";

		private PreparedStatementScriptInfo(String preparedStatementScritp,
										    long timeout) {
			this.preparedStatementScript = preparedStatementScritp;
			this.timeout = timeout;
		}
		
		private void startAutoReturnMechanism() {
			synchronized (this.TIMERNAME) {
				doUpdate();
				
				// Create Timer
				if (null == this.autoReturnStatementTimer) {
					this.autoReturnStatementTimer = new Timer(TIMERNAME, true);
				}
				
				// Create TimerTask
				// 確保只有一個 TimerTask 在 Timer中
				if (null != this.autoReturnStatementTimerTask) {
					this.autoReturnStatementTimerTask.cancel();
				}
				this.autoReturnStatementTimerTask = new AutoReturnMechanism(this);
				
				this.autoReturnStatementTimer.schedule(this.autoReturnStatementTimerTask,
												 	   timeout, timeout);
			}
		}
		
		private void stopAutoReturnMechanism() {
			synchronized (this.TIMERNAME) {
				if (null != this.autoReturnStatementTimerTask) {
					this.autoReturnStatementTimerTask.cancel();
					this.autoReturnStatementTimerTask = null;
				}
			}
		}
		
		private String getPreparedStatementScript() {
			return preparedStatementScript;
		}

		private void doUpdate() {
			motionCheck = true;
		}

		private void doCheckPoint() {
			motionCheck = false;
		}

		private boolean getMotionCheck() {
			return motionCheck;
		}
	}

	public static Object[] sortValuesByGivingFieldVector(Map<String, Object> valuesMap, 
														 Vector<String> filedOrderVector) {
		List<Object> orderedValuesList = new LinkedList<Object>();
		
		for (String fieldName : filedOrderVector) {
			orderedValuesList.add(valuesMap.get(fieldName));
		}
		
		return orderedValuesList.toArray();
	}
	
	
	
	/**
	 * 
	 * @author p10118178
	 * 
	 */
	private class AutoReturnMechanism extends TimerTask {
		private PreparedStatementScriptInfo preparedStatementScriptInfo;

		private AutoReturnMechanism(PreparedStatementScriptInfo preparedStatementScriptInfo) {
			this.preparedStatementScriptInfo = preparedStatementScriptInfo;
		}

		@Override
		public void run() {
			if (null == preparedStatementScriptInfo) {
				this.cancel();
				return;
			}
			
			if (!preparedStatementScriptInfo.getMotionCheck()) {
				removePreparedStatementInPool(preparedStatementScriptInfo);
			} else {
				preparedStatementScriptInfo.doCheckPoint();
			}
		}

	}
	
	// --- getter and setter ---
	
	public void setSqlExceptionTranslator(
			SQLExceptionTranslator sqlExceptionTranslator) {
		this.sqlExceptionTranslator = sqlExceptionTranslator;
	}
	
}
